package com.swan.models;

import com.swan.beans.ClassBase;
import com.swan.beans.ClassRegister;
import com.swan.beans.StudentClassReg;
import com.swan.beans.SeasonClass;
import com.swan.exceptions.CustomerException;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ClassDao {

    public boolean addClass(ClassBase classBase) {
        return DBUtil.doSomething(db -> {
            int val = db.updateWithMode("insert into class_base " +
                            "(title, subject, description, course_number, `section`, hours, crn, term, instructor, seats,time_frame, campus, attribute)\n" +
                            "values (?,?,?,?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS, classBase.getTitle(), classBase.getSubject(), classBase.getDescription(),
                    classBase.getCourseNumber(), classBase.getSection(), classBase.getHours(), classBase.getCrn(),
                    classBase.getTerm(), classBase.getInstructor(), classBase.getSeats(), classBase.getTimeFrame(),
                    classBase.getCampus(), classBase.getAttribute()
            );
            classBase.setId(db.getGeneratedKey());
            return val == 1;
        });
    }

    public void bindSeasonClass(int seasonId, int classId) {
        DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select * from season_class where season_id = ? and class_id = ? ", seasonId, classId);
                if (rs.next())
                    throw new CustomerException("Class had been relate to the season");
                return db.updateWithoutGeneratedKey("insert into season_class (season_id,class_id) values (?,?)", seasonId, classId) == 1;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }

    public void unBindSeasonClass(int seasonId, int classId) {
        DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select * from season_class where season_id = ? and class_id = ? ", seasonId, classId);
                if (rs.next()) {
                    return db.updateWithoutGeneratedKey("delete from season_class where id = ?", rs.getInt("id"));
                } else
                    throw new CustomerException("Class isn't bind to the season");
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }
    
    
    

    public void registerClass(int userId, int seasonClassId) {
        DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select * from class_register where user_id = ? and season_class_id = ? ", userId, seasonClassId);
                if (rs.next())
                    throw new CustomerException("User had registered the class.");
                return db.updateWithoutGeneratedKey("insert into class_register (user_id,season_class_id) values (?,?)", userId, seasonClassId) == 1;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }

    public void removeRegisterClass(int userId, int seasonClassId) {
        DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select * from class_register where user_id = ? and season_class_id = ? ", userId, seasonClassId);
                if (rs.next()) {
                    return db.updateWithoutGeneratedKey("delete from class_register where id = ?", rs.getInt("id"));
                } else
                    throw new CustomerException("User haven't register the class.");
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        });
    }

    public boolean removeClass(int id) {
        return DBUtil.doSomething(db -> db.updateWithoutGeneratedKey("delete from class_base where id = ?", id) == 1);
    }

    public List<ClassBase> list() {
        return DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select * from class_base");
                ArrayList<ClassBase> ret = new ArrayList<>();
                while (rs.next()) {
                    ret.add(getClassBase(rs));
                }
                return ret;
            } catch (SQLException e) {
                throw new CustomerException(e);
            }
        });
    }
    
    public List<SeasonClass> listSeasonClass() {
        return DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select sc.id ,s.name,s.id as seasonId,cb.title,cb.id as classId from season_class sc,class_base cb,season s where sc.class_id =cb.id and sc.season_id =s.id ");
                ArrayList<SeasonClass> ret = new ArrayList<>();
                while (rs.next()) {
                    ret.add(getSeasonClass(rs));
                }
                return ret;
            } catch (SQLException e) {
                throw new CustomerException(e);
            }
        });
    }
    
    public List<ClassRegister> listClassRegister(int userId) {
        return DBUtil.doSomething(db -> {
            try {
            	String sql="select cr.id,sc.id as season_class_id,s.name,title, subject, description, course_number, `section`, hours, crn, term, instructor, seats, time_frame, campus, `attribute`,cr.user_id \r\n"
            			+ "from season_class sc \r\n"
            			+ "left join season s on sc.season_id =s.id \r\n"
            			+ "left join class_base cb on sc.class_id =cb.id \r\n"
            			+ "left join class_register cr on sc.id =cr.season_class_id and cr.user_id = ?\r\n"
            			+ "";
                ResultSet rs = db.preparedStatement(sql,userId);
                ArrayList<ClassRegister> ret = new ArrayList<>();
                while (rs.next()) {
                    ret.add(getClassRegister(rs));
                }
                return ret;
            } catch (SQLException e) {
                throw new CustomerException(e);
            }
        });
    }
    
    public List<StudentClassReg> listStudentClassReg() {
        return DBUtil.doSomething(db -> {
            try {
            	String sql="select a.name as user_name ,s.name as season_name ,cb.title as class_title\r\n"
            			+ "from class_register cr \r\n"
            			+ "left join account a on cr.user_id =a.id \r\n"
            			+ "left join season_class sc on cr.season_class_id =sc.id \r\n"
            			+ "left join season s on sc.season_id =s.id \r\n"
            			+ "left join class_base cb on sc.class_id =cb.id ";
                ResultSet rs = db.preparedStatement(sql);
                ArrayList<StudentClassReg> ret = new ArrayList<>();
                while (rs.next()) {
                    ret.add(getStudentClassReg(rs));
                }
                return ret;
            } catch (SQLException e) {
                throw new CustomerException(e);
            }
        });
    }
    

    public List<ClassBase> getByTitle(String classTitle) {
        try (DBUtil db = new DBUtil()) {
            ResultSet rs = db.preparedStatement("select * from class_base where title = ?", classTitle);
            List<ClassBase> ret = new ArrayList<>();
            while (rs.next()) {
                ret.add(getClassBase(rs));
            }
            return ret;
        } catch (SQLException e) {
            throw new CustomerException(e);
        }
    }

    public ClassBase getById(String id) {
        return DBUtil.doSomething(db -> {
            try {
                ResultSet rs = db.preparedStatement("select * from class_base where id = ?", id);
                if (rs.next())
                    return getClassBase(rs);
                else
                    return null;
            } catch (SQLException ex) {
                throw new CustomerException(ex);
            }
        });
    }

    private ClassBase getClassBase(ResultSet rs) {
        try {
            return new ClassBase().setId(rs.getInt("id"))
                    .setTitle(rs.getString("title"))
                    .setSubject(rs.getString("subject"))
                    .setDescription(rs.getString("description"))
                    .setCrn(rs.getInt("crn"))
                    .setCampus(rs.getString("campus"))
                    .setAttribute(rs.getString("attribute"))
                    .setHours(rs.getInt("hours"))
                    .setCourseNumber(rs.getString("course_number"))
                    .setInstructor(rs.getString("instructor"))
                    .setSection(rs.getInt("section"))
                    .setSeats(rs.getInt("seats"))
                    .setTerm(rs.getInt("term"))
                    .setTimeFrame(rs.getString("time_frame"))
                    ;
        } catch (SQLException ex) {
            throw new CustomerException(ex);
        }
    }

    
    private SeasonClass getSeasonClass(ResultSet rs) {
    	 try {
             return new SeasonClass().setId(rs.getInt("id"))
                     .setName(rs.getString("name"))
                     .setTitle(rs.getString("title"))
                     .setSeasonId(rs.getInt("seasonId"))
                     .setClassId(rs.getInt("classId"))
                     ;
         } catch (SQLException ex) {
             throw new CustomerException(ex);
         }
    }
    
    private ClassRegister getClassRegister(ResultSet rs) {
        try {
            return new ClassRegister().setId(rs.getInt("id"))
            		.setSeasonClassId(rs.getInt("season_class_id"))
                    .setName(rs.getString("name"))
                    .setTitle(rs.getString("title"))
                    .setSubject(rs.getString("subject"))
                    .setDescription(rs.getString("description"))
                    .setCrn(rs.getInt("crn"))
                    .setCampus(rs.getString("campus"))
                    .setAttribute(rs.getString("attribute"))
                    .setHours(rs.getInt("hours"))
                    .setCourseNumber(rs.getString("course_number"))
                    .setInstructor(rs.getString("instructor"))
                    .setSection(rs.getInt("section"))
                    .setSeats(rs.getInt("seats"))
                    .setTerm(rs.getInt("term"))
                    .setTimeFrame(rs.getString("time_frame"))
                    .setUserId(rs.getInt("user_id"))
                    .setReg(rs.getInt("user_id")==0?"NO":"YES")
                    ;
        } catch (SQLException ex) {
            throw new CustomerException(ex);
        }
    }
    
    private StudentClassReg getStudentClassReg(ResultSet rs) {
        try {
            return new StudentClassReg().setUserName(rs.getString("user_name"))
                    .setSeasonName(rs.getString("season_name"))
                    .setClassTitle(rs.getString("class_title"))
                    ;
        } catch (SQLException ex) {
            throw new CustomerException(ex);
        }
    }
}
